﻿using DeHeng_Mes.Constant;
using DeHeng_Mes.Model.ConfigModel;
using DeHeng_Mes.Model.OrderSingleton;
using DeHeng_Mes.Model.PlcModel;
using DeHeng_Mes.Model.PlcModel.door_01;
using DeHeng_Mes.Model.Table;
using DeHeng_Mes.Utils;
using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;

namespace DeHeng_Mes.Dao
{
    public class OrderDao
    {

        #region
        
        #endregion

        /// <summary>
        /// 获取订单状态
        /// </summary>
        /// <param name="orderId"></param>
        /// <returns></returns>
        public bool GetOrderStatus(string orderId)
        {
            using (var db = SqlSugarConfig.Db)
            {
                return db.Queryable<OrderStatus>()
                      .Where(x => x.OrderId == orderId)
                      .Any();
            }
        }

        /// <summary>
        /// 获取订单信息
        /// </summary>
        /// <param name="orderId"></param>
        /// <returns></returns>
        public OrderStatus GetOrder(string orderId)
        {
            using (var db = SqlSugarConfig.Db)
            {
                return db.Queryable<OrderStatus>()
                    .Where(x => x.OrderId == orderId)
                    .First(); // 返回单个实体
            }
        }


        /// <summary>
        /// 插入新订单信息
        /// </summary>
        /// <param name="orderStatus"></param>
        /// <returns></returns>
        public int InsertOrder(OrderStatus orderStatus)
        {
            using (var db = SqlSugarConfig.Db)
            {
                return db.Insertable(orderStatus)
                  .ExecuteCommand();
            }
        }

        /// <summary>
        /// 完成订单修改对应信息
        /// </summary>
        /// <param name="orderStatus"></param>
        /// <returns></returns>
        public void UpdateOrder(string wo_code, string wo_status)
        {
            using (var db = SqlSugarConfig.Db)
            {
                db.Updateable<OrderStatus>()
                  .SetColumns(x => x.EndTime == DateTime.Now)
                  .SetColumns(x => x.Status == wo_status)
                  .Where(x => x.OrderId == wo_code)
                  .ExecuteCommand();
            }
        }

        /// <summary>
        /// 判断拉铆数据是否保存
        /// </summary>
        /// <param name="code"></param>
        /// <param name="pointCount"></param>
        /// <returns></returns>
        public bool RiveteDataIsSave(string code, string pointCount, string station)
        {
            using (var db = SqlSugarConfig.Db)
            {
                return db.Queryable<TraceRivete>()
                    .Where(x => x.TraceId == code && x.PointCount == pointCount && x.Station == station)
                    .AS($"trace_rivete{HashUtils.GetHashAsInt(code, 10)}")
                    .Any();
            }
        }

        /// <summary>
        /// 存储拉铆数据
        /// </summary>
        /// <param name="traceRivete"></param>
        public void SaveRiveteData(TraceRivete traceRivete)
        {
            using (var db = SqlSugarConfig.Db)
            {
                db.Insertable(traceRivete)
                  .AS($"trace_rivete{HashUtils.GetHashAsInt(traceRivete.TraceId, 10)}")
                  .ExecuteCommand();
            }
        }

        /// <summary>
        /// 查询过点数据是否保存
        /// </summary>
        /// <param name="station"></param>
        /// <param name="startTime"></param>
        public bool ProcessDataIsSave(string station, DateTime startTime)
        {
            using (var db = SqlSugarConfig.Db)
            {
                return db.Queryable<PartProcess>()
                       .Where(x => x.Station == station && x.StartTime == startTime)
                       .Any();
            }
        }

        /// <summary>
        /// 存储过点数据
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int ProcessDataSave(StationData data, string operation, string station)
        {
            var partProcess = new PartProcess
            {
                Operation = operation,
                Station = station,
                Batch = data.Batch,
                Type = data.Type,
                Worker = data.Worker,
                RBNo = data.RBNo,
                StartTime = data.StartTime,
                EndTime = data.EndTime,
                Beat = data.Beat.ToString(),
                Result = data.Result.ToString().Equals("1") ? "OK" : "NG",
            };

            using (var db = SqlSugarConfig.Db)
            {
                var processId = db.Insertable(partProcess)
                       .ExecuteReturnIdentity();

                Tools.ProduceLogText($"插入了一条过点数据，工位号[{data.Station}]，开始时间[{data.StartTime}]，结束时间[{data.EndTime}]");

                return processId;
            }
        }

        /// <summary>
        /// 检查焊接数据是否保存,保存则返回主键
        /// </summary>
        /// <param name="seamNo"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public int WeldDataIsSave(TraceWeld traceWeld)
        {
            using (var db = SqlSugarConfig.Db)
            {
                // 查找符合条件的记录
                var traceWeldId = db.Queryable<TraceWeld>()
                                  .Where(x => x.SeamNo == traceWeld.SeamNo && x.StartTime == traceWeld.StartTime && x.EndTime == traceWeld.EndTime && x.RobotNo == traceWeld.RobotNo)
                                  .Select(x => x.Id)
                                  .First();

                if (traceWeldId == 0)
                {
                    return db.Insertable(traceWeld)
                         .ExecuteReturnIdentity();
                }

                return traceWeldId;
            }
        }

        /// <summary>
        /// 存储数据，并且返回主键
        /// </summary>
        /// <param name="traceWeld"></param>
        /// <returns></returns>
        public string WeldDataSave(TraceWeld traceWeld)
        {
            using (var db = SqlSugarConfig.Db)
            {
                Tools.ProduceLogTextError($"[{traceWeld.Station}][{traceWeld.SeamNo}][{traceWeld.RobotNo}]");

                return db.Insertable(traceWeld)
                         .ExecuteReturnIdentity()
                         .ToString();
            }
        }

        /// <summary>
        /// 检查追溯码和过点数据是否保存
        /// </summary>
        /// <param name="code"></param>
        /// <param name="processId"></param>
        /// <returns></returns>
        public bool CodeAndProcessAndWeldIsSave(int code, string processId, string operation)
        {
            using (var db = SqlSugarConfig.Db)
            {
                return db.Queryable<CodeOperation>()
                         .Where(x => x.TraceId == code && x.WeldIds == processId && x.Operation == operation)
                         .Any();
            }
        }

        /// <summary>
        /// 存储追溯码和过点数据、焊接数据
        /// </summary>
        /// <param name="code"></param>
        /// <param name="processId"></param>
        /// <param name="weldIdsStr"></param>
        public void DataSave(int code, string weldIdsStr, string Operation)
        {
            using (var db = SqlSugarConfig.Db)
            {
                db.Insertable(new CodeOperation()
                {
                    Operation = Operation,
                    WeldIds = weldIdsStr,
                    TraceId = code
                }).ExecuteCommand();

                Tools.ProduceLogText($"成功绑定了一条追溯码、焊接数据的关联信息，追溯码标识[{code}]，焊接数据标识[{weldIdsStr}]");
            }
        }

        /// <summary>
        /// 存储批次码
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public int SaveBatchCode(string code, string station)
        {
            using (var db = SqlSugarConfig.Db)
            {
                bool isSave = db.Queryable<TraceBatchCode>().Any(x => x.BatchId == code);

                if (!isSave)
                {
                    int id = db.Insertable(new TraceBatchCode()
                    {
                        BatchId = code,
                    }).ExecuteReturnIdentity();

                    Tools.ProduceLogText($"批次码表插入了一条数据，批次码为[{code}]");

                    return id;
                }
                else
                {
                    return 0;
                }
            }
        }

        /// <summary>
        /// 绑定精追码表数据
        /// </summary>
        /// <param name="traceCode"></param>
        /// <returns></returns>
        public int SaveCode(string traceCode, PlcOrderConfigModel orderModel)
        {
            using (var db = SqlSugarConfig.Db)
            {
                int id = db.Queryable<TraceCode>().Where(x => x.TraceId == traceCode).Select(x => x.Id).First();

                if (OrderSingleton.Instance?.Count > 0)
                {
                    // 工单信息
                    OrderSingletonModel orderSingletonModel = OrderSingleton.Instance.Where(x => x.PlcName == orderModel.PlcName).FirstOrDefault();

                    if (orderModel.PlcName.Equals("Rivet"))
                    {
                        orderSingletonModel = OrderSingleton.Instance.Where(x => x.MaterialId.Equals("100401002101")).FirstOrDefault();
                    }

                    if (orderSingletonModel != null)
                    {
                        // 绑定orderTrace
                        db.Insertable(new OrderTrace()
                        {
                            OrderId = orderSingletonModel.OrderId,
                            TraceId = traceCode,
                            CreateTime = DateTime.Now,
                            MaterialPushStatus = "0",
                            OperationPushStatus = "0",
                            PartPushStatus = "0",
                            StationPushStatus = "0",
                        }).ExecuteCommand();
                    }
                }

                if (id == 0)
                {
                    int id2 = db.Insertable(new TraceCode()
                    {
                        TraceId = traceCode,
                        CreateTime = DateTime.Now,
                    }).ExecuteReturnIdentity();

                    Tools.ProduceLogText($"精追码表插入了一条数据，精追码为[{traceCode}]");

                    return id2;
                }
                else
                {
                    return id;
                }
            }
        }

        /// <summary>
        /// 绑定追溯码与批次追溯码关联关系
        /// </summary>
        /// <param name="traceId"></param>
        /// <param name="batchId"></param>
        /// <param name="count"></param>
        /// <param name="station"></param>
        public void BandingCodeAndBatchCode(int traceId, int batchId, string count, string station)
        {
            using (var db = SqlSugarConfig.Db)
            {
                bool isSave = db.Queryable<CodeBatchCode>().Any(x => x.TraceId == traceId && x.BatchId == batchId && x.BatchCount == int.Parse(count) && x.Station == station);

                if (!isSave)
                {
                    db.Insertable(new CodeBatchCode()
                    {
                        BatchCount = int.Parse(count),
                        BatchId = batchId,
                        Station = station,
                        TraceId = traceId,
                        CreateTime = DateTime.Now,
                        IsSave = "0"
                    }).ExecuteCommand();

                    Tools.ProduceLogText($"绑定了一条追溯码与批次追溯码关联关系，追溯码为[{traceId}]，批次码为[{batchId}]，本批投料[{count}]，本批工序[{station}]");
                }
            }
        }

        /// <summary>
        /// 绑定精确码关联关系
        /// </summary>
        /// <param name="traceId"></param>
        /// <param name="station"></param>
        public void BandingCodeAndCode(int traceId, string station)
        {
            using (var db = SqlSugarConfig.Db)
            {
                bool isSave = db.Queryable<TraceAssociation>().Any(x => x.TraceId == traceId && x.Station == station);

                if (!isSave)
                {
                    db.Insertable(new TraceAssociation()
                    {
                        Station = station,
                        TraceId = traceId,
                    }).ExecuteCommand();

                    Tools.ProduceLogText($"绑定了一条追溯码与工序关联关系，追溯码为[{traceId}]，工序为[{station}]");
                }
            }
        }

        /// <summary>
        /// 存储过点数据与追溯码关联关系
        /// </summary>
        /// <param name="traceId"></param>
        /// <param name="processId"></param>
        /// <param name="station"></param>
        public void BandingProcessAndCode(int traceId, int processId, string station)
        {
            using (var db = SqlSugarConfig.Db)
            {
                bool isSave = db.Queryable<CodeProcess>().Any(x => x.TraceId == traceId && x.Station == station && x.ProcessId == processId);

                if (!isSave)
                {
                    db.Insertable(new CodeProcess()
                    {
                        Station = station,
                        TraceId = traceId,
                        ProcessId = processId,
                        IsSave = "0"
                    }).ExecuteCommand();

                    Tools.ProduceLogText($"绑定了追溯码与过点数据关联关系，追溯码为[{traceId}]，过点数据标识[{processId}]，工序为[{station}]");
                }
            }
        }

        /// <summary>
        /// 查找未结束的工单
        /// </summary>
        public List<OrderStatus> QueryOrder()
        {
            using (var db = SqlSugarConfig.Db)
            {
                return db.Queryable<OrderStatus>()
                         .Where(x => x.Status != "9")
                         .ToList();
            }
        }

        /// <summary>
        /// 查询所有未推送的数据
        /// </summary>
        /// <returns></returns>
        public List<OrderTrace> QueryOrderTrace(string orderId)
        {
            try
            {
                using (var db = SqlSugarConfig.Db)
                {
                    List<OrderTrace> orderTraces = db.Queryable<OrderTrace>().Where(x => (x.PartPushStatus.Equals("0") || x.StationPushStatus.Equals("0")) && x.OrderId == orderId).ToList();

                    return orderTraces;
                }
            }
            catch (Exception ex)
            {
                Tools.ProduceLogTextError($"查询未推送数据失败，原因是{ex.Message}");

                return null;
            }
        }

        /// <summary>
        /// 更新上件数据推送状态
        /// </summary>
        public void UpdatePartPushStatus(string orderId, string traceId)
        {
            try
            {
                using (var db = SqlSugarConfig.Db)
                {
                    db.Updateable<OrderTrace>()
                        .SetColumns(x => x.PartPushStatus == "1")
                        .Where(x => x.OrderId == orderId && x.TraceId == traceId)
                        .ExecuteCommand();
                }
            }
            catch (Exception e)
            {
                Tools.ProduceLogTextError($"更新单件推送状态失败，原因是{e.Message}");
            }
        }

        /// <summary>
        /// 根据二维码获取主键
        /// </summary>
        /// <param name="code">二维码</param>
        /// <returns>二维码对应的主键</returns>
        public int GetCodeId(string code)
        {
            try
            {
                using (var db = SqlSugarConfig.Db)
                {
                    var result = db.Queryable<TraceCode>()
                                   .Where(x => x.TraceId == code)
                                   .Select(x => x.Id)
                                   .First();

                    return result;
                }
            }
            catch (InvalidOperationException)
            {
                return 0;  // 自定义的错误值
            }
        }

        /// <summary>
        /// 获取过站信息与二维码映射表
        /// </summary>
        /// <param name="traceId"></param>
        /// <returns></returns>
        public List<int> GetProcessIds(int traceId)
        {
            using (var db = SqlSugarConfig.Db)
            {
                return db.Queryable<CodeProcess>().Where(x => x.TraceId == traceId && x.IsSave.Equals("0")).Select(x => x.ProcessId).ToList();
            }
        }

        /// <summary>
        /// 根据追溯码Id获取批次投料
        /// </summary>
        /// <param name="traceId"></param>
        /// <returns></returns>
        public List<CodeBatchCode> GetBtachCodeList(int traceId)
        {
            using (var db = SqlSugarConfig.Db)
            {
                List<CodeBatchCode> codeBatchCodes = db.Queryable<CodeBatchCode>().Where(x => x.TraceId == traceId).ToList();

                return codeBatchCodes;
            }
        }

        /// <summary>
        /// 根据主键查询数据
        /// </summary>
        /// <param name="processId"></param>
        /// <returns></returns>
        public PartProcess GetProcessData(int processId)
        {
            using (var db = SqlSugarConfig.Db)
            {
                return db.Queryable<PartProcess>().Where(x => x.Id == processId).First();
            }
        }

        /// <summary>
        /// 更新过站数据推送
        /// </summary>
        /// <param name="traceId"></param>
        /// <param name="key"></param>
        public bool UpDateStationStatus(int traceId, string station)
        {
            using (var db = SqlSugarConfig.Db)
            {
                return db.Updateable<CodeProcess>().SetColumns(x => x.IsSave == "1").Where(x => x.TraceId == traceId && station == station).ExecuteCommand() > 0;
            }
        }

        /// <summary>
        /// 根据工单与追溯码更新工位数据推送状态
        /// </summary>
        /// <param name="orderId"></param>
        /// <param name="traceId"></param>
        public void UpDateStationStatus(string orderId, string traceId)
        {
            using (var db = SqlSugarConfig.Db)
            {
                db.Updateable<OrderTrace>().SetColumns(x => x.StationPushStatus == "1").Where(x => x.OrderId == orderId && x.TraceId == traceId).ExecuteCommand();
            }
        }

        /// <summary>
        /// 根据追溯码查询批次主键信息
        /// </summary>
        /// <param name="traceId"></param>
        /// <returns></returns>
        public List<CodeBatchCode> GetBatchIds(int traceId)
        {
            using (var db = SqlSugarConfig.Db)
            {
                return db.Queryable<CodeBatchCode>().Where(x => x.TraceId == traceId && x.IsSave.Equals("0")).ToList();
            }
        }

        /// <summary>
        /// 根据批次主键查询批次数据
        /// </summary>
        /// <param name="batchId"></param>
        /// <returns></returns>
        public TraceBatchCode GetBatchData(int batchId)
        {
            using (var db = SqlSugarConfig.Db)
            {
                return db.Queryable<TraceBatchCode>().Where(x => x.Id == batchId).First();
            }
        }

        /// <summary>
        /// 更新批次码更新状态
        /// </summary>
        /// <param name="batchId"></param>
        public void UpdateBatchStatus(CodeBatchCode batchId)
        {
            using (var db = SqlSugarConfig.Db)
            {
                db.Updateable<CodeBatchCode>().Where(x => x.TraceId == batchId.TraceId && x.BatchId == batchId.BatchId && x.BatchCount == batchId.BatchCount).SetColumns(x => x.IsSave == "1").ExecuteCommand();
            }
        }

        /// <summary>
        /// 检查二维码是否存储
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public List<int> CodeIsSave(string code)
        {
            using (var db = SqlSugarConfig.Db)
            {
                return db.Queryable<TraceCode>().Where(x => x.TraceId == code).Select(x => x.Id).ToList();
            }
        }
        
        /// <summary>
        /// 检查该二维码是否存在该工位数据
        /// </summary>
        /// <param name="code"></param>
        /// <param name="station"></param>
        /// <returns></returns>
        public bool CodeIsSave(int id, string station)
        {
            using (var db = SqlSugarConfig.Db)
            {
                if (!string.IsNullOrEmpty(station) && station.Contains("ST30"))
                {
                    string[] validStations = { SystemConstant.ST30A, SystemConstant.ST30B };
                    return db.Queryable<CodeProcess>()
                             .Any(x => x.TraceId == id && validStations.Contains(x.Station));
                }
                else
                {
                    return db.Queryable<CodeProcess>()
                             .Any(x => x.TraceId == id && x.Station == station);
                }
            }
        }

        /// <summary>
        /// 备份扫码校验记录
        /// </summary>
        /// <param name="code"></param>
        public void BackUpCode(string code, string station)
        {
            try
            {
                using (var db = SqlSugarConfig.Db)
                {
                    bool v = db.Queryable<BackUpCode>().Any(x => x.TraceId == code && x.Station == station);

                    if (!v)
                    {
                        db.Insertable(new BackUpCode()
                        {
                            CreateTime = DateTime.Now,
                            TraceId = code,
                            Station = station
                        }).ExecuteCommand();
                    }
                }
            }
            catch
            {

            }
        }

        /// <summary>
        /// 更新工单计划数量
        /// </summary>
        /// <param name="wo_code"></param>
        /// <param name="plan_num"></param>
        /// <returns></returns>
        public bool UpdateOrderNum(string wo_code, int plan_num)
        {
            using (var db = SqlSugarConfig.Db)
            {
                int count = db.Updateable<OrderStatus>()
                              .SetColumns(x => x.ProductionPlan == plan_num.ToString())
                              .Where(x => x.OrderId == wo_code)
                              .ExecuteCommand();

                return count > 0;
            }
        }

        public OrderTrace GetOrderCreatetime(string traceId)
        {
            using (var db = SqlSugarConfig.Db)
            {
                return db.Queryable<OrderTrace>().Where(x => x.TraceId == traceId).First();
            }
        }
    }
}
